﻿using NPOI.XSSF.UserModel; // 对于.xlsx文件
using NPOI.SS.UserModel;
using System.IO;
using Model;
using NPOI.SS.Formula.Functions;
using NPOI.POIFS.Crypt.Dsig;
using System.Reflection.PortableExecutable;

//展示怎么用的模板，不能引用
public class ExcelHelper
{
    /// <summary>
    /// 导入功能
    /// </summary>
    /// <param name="filePath">Excel路径</param>
    /// <returns></returns>
    public List<PurchaseOrder> export(string filePath)
    {
        using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
        {
            IWorkbook workbook = new XSSFWorkbook(fs); // 对于.xlsx文件
                                                       // IWorkbook workbook = new HSSFWorkbook(fs); // 对于.xls文件

            // 获取第一个工作表
            ISheet sheet = workbook.GetSheetAt(0);

            //任何想生成的List
            List<PurchaseOrder> purchaseOrders = new List<PurchaseOrder>();
            // 遍历行
            for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)
            {
                IRow row = sheet.GetRow(rowIndex);
                if (row != null)
                {
                    PurchaseOrder purchaseOrder = new PurchaseOrder();

                    purchaseOrder.SupplierCode = row.GetCell(0).ToString();

                    purchaseOrders.Add(purchaseOrder);
                }
            }
            return purchaseOrders;
        }
    }


    //导出功能
    public MemoryStream import(List<PurchaseOrder> purchaseOrders)
    {
        // 创建Excel工作簿
        IWorkbook workbook = new XSSFWorkbook();

        // 添加工作表
        ISheet sheet = workbook.CreateSheet("purchaseOrders");

        //绘制表头
        IRow headerRow = sheet.CreateRow(0);
        headerRow.CreateCell(0).SetCellValue("工单");
        headerRow.CreateCell(1).SetCellValue("供应商");
        headerRow.CreateCell(2).SetCellValue("物料号");


        // 填充数据
        int rowIndex = 1;
        foreach (var rowData in purchaseOrders)
        {
            IRow dataRow = sheet.CreateRow(rowIndex);
            dataRow.CreateCell(0).SetCellValue(rowData.Order);
            dataRow.CreateCell(1).SetCellValue(rowData.SupplierCode);
            dataRow.CreateCell(2).SetCellValue(rowData.MaterialCode);

            rowIndex++;
        }
        using (MemoryStream ms = new MemoryStream())
        {
            workbook.Write(ms);
            var ms2 = new MemoryStream(ms.ToArray());
            ms2.Flush();
            ms2.Position = 0;
            return ms2;
        }
        // 写入到文件
        
        
        
        
    }
}